package com.boot.study.config;

import cn.hutool.core.io.resource.FileResource;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.annotation.DbType;
import org.apache.ibatis.jdbc.ScriptRunner;

import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @Description
 * @Author likz 数据库工具类
 * @Date 2024/8/7 9:32
 **/
public class JdbcUtils {


    /**
     * 判断是否连接成功
     * @param url
     * @param username
     * @param password
     * @return
     */
    public static boolean isConnectionOK(String url, String username, String password) {
        try (Connection ignored = DriverManager.getConnection(url, username, password)) {
            // 尝试获取数据库连接，如果成功，则连接正常
            return true;
        } catch (Exception ex) {
            // 如果发生异常，则连接不成功
            return false;
        }
    }

    /**
     * 是否创建数据库成功
     * @param url
     * @param username
     * @param password
     * @param databaseName
     * @return
     */
    public static boolean isCreateDatabaseOK(String url, String username, String password, String databaseName) {
        try (Connection ignored = DriverManager.getConnection(url, username, password)) {
            Statement statement = ignored.createStatement();
            //先删除数据库
            statement.execute("DROP DATABASE IF EXISTS " + databaseName);
            //在创建数据库
            statement.execute("CREATE DATABASE IF NOT EXISTS " + databaseName);
            // 关闭Statement和Connection
            statement.close();
            ignored.close();
            // 尝试获取数据库连接，如果成功，则连接正常
            return true;
        } catch (Exception ex) {
            // 如果发生异常，则连接不成功
            return false;
        }
    }

    /**
     * 创建数据库用户
     * @param url
     * @param databaseUsername
     * @param databasePassword
     * @param databaseName
     * @param user
     * @param password
     * @return
     */
    public static boolean isCreateDatabaseUserOK(String url, String databaseUsername, String databasePassword, String databaseName, String user, String password) {
        try (Connection ignored = DriverManager.getConnection(url, databaseUsername, databasePassword)) {
            Statement statement = ignored.createStatement();
            //创建用户
            statement.executeUpdate("CREATE USER '" + user + "'@'%' IDENTIFIED BY '" + password + "'");
            //设置权限
            statement.execute("GRANT Alter, Alter Routine, Create, Create Routine, Create Temporary Tables, Create View, Delete, Drop, Event, Execute, Index, Insert, Lock Tables, References, Select, Show View, Trigger, Update ON `"+databaseName+"`.* TO `"+user+"`@`%`;");
            // 刷新权限
            statement.execute("FLUSH PRIVILEGES");
            // 关闭Statement和Connection
            statement.close();
            ignored.close();
            // 尝试获取数据库连接，如果成功，则连接正常
            return true;
        } catch (Exception ex) {
            // 如果发生异常，则连接不成功
            return false;
        }
    }

    /**
     * 运行sql文件
     * @param url
     * @param username
     * @param password
     * @param sqlPath
     * @return
     */
    public static boolean initDB(String url, String username, String password, String sqlPath) {
        try (Connection ignored = DriverManager.getConnection(url, username, password)) {
            ScriptRunner scriptRunner = new ScriptRunner(ignored);
            scriptRunner.setStopOnError(true);
            FileResource classPathResource = new FileResource(sqlPath);
            InputStream inputStream = classPathResource.getStream();
            InputStreamReader isr = new InputStreamReader(inputStream);
            scriptRunner.runScript(isr);
            return true;
        } catch (Exception ex) {
            // 如果发生异常，则连接不成功
            return false;
        }
    }

    /**
     * 获取数据库类型
     * @param url
     * @return
     */
    public static DbType getDbType(String url) {
        // 使用Druid工具类获取数据库类型名称
        String name = com.alibaba.druid.util.JdbcUtils.getDbType(url, null);
        // 根据数据库类型名称获取对应的枚举类型
        return DbType.getDbType(name);
    }

    /**
     * 配置数据库参数
     * @return
     */
    public static DruidDataSource getInitDBConfig(){
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setInitialSize(5);
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(20);
        dataSource.setMaxWait(60000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("select 1 from dual");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        return dataSource;
    }


}
